#!/bin/bash

# 定义变量方便修改
table_db=cy_database
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql="
with
tmp1_ent_nature as
(
select
	num,
	cast((num/amount) as decimal(10,5)) as percent1,
	content,
	'企业性质' as name,
	current_date() operate_time
from(

	select
		content,num,amount
	from(
		select
			content,num,amount,
			row_number() over(partition by content) as rn
		from (
			select
				content,
				count(1) over(partition by content) as num,
				count(1) over() as amount
			from (
				SELECT
					case when business_nature_code = '1000' then '国有企业'
						when  business_nature_code = '1001' then '私营企业'
						when business_nature_code = '1002' then '股份制企业'
						end as content
				from cy_database.dws_enterprise_position_require_detail
				where business_nature_code is not null
				)a
			where content is not null
			)aa
	    )aaa
	where rn=1
	)b
),


tmp2_table as
(
select
	num,
	null  percent1,
	'岗位需求总量' content,
	'岗位需求总量' name,
	dt as operate_time
from (
	select
		dt,
		sum(posts) as num
	from cy_database.dws_enterprise_position_require_detail
	where dt='2020-06-17'
	group by dt
	)a
),


tmp3_table as
(
select
	num,
	cast((num/amount) as decimal(10,5)) as percent1,
	content,
	'企业规模' as name,
	current_date() operate_time
from(

	select
		content,num,amount
	from(
		select
			content,num,amount,
			row_number() over(partition by content) as rn
		from (
			select
				content,
				count(1) over(partition by content) as num,
				count(1) over() as amount
			from (
				SELECT
					case when scale_code = '1000' then '小于50人'
						when  scale_code = '1001' then '51-100人'
						when scale_code = '1002' then '101-500人'
						when scale_code = '1003' then '大于500人'
						end as content
				from cy_database.dws_enterprise_info
				where scale_code is not null
				)a
			where content is not null
			)aa
	    )aaa
	where rn=1
	)b
),



tmp4_table as
(
select
	num,
	null percent1,
	'招聘企业' content,
	'招聘企业' name,
	dt as operate_time
from (
	select
		count(1) over() num,
		dt
	from (
		select
			ent_name,
			dt
		from cy_database.dws_enterprise_position_require_detail
		where dt = '2020-06-17' and ent_name is not null
		group by dt,ent_name
		)a
	limit 1
	)aa
),



tmp5_table as
(
	select
		num,
		null percent1,
		'招聘岗位' content,
		'招聘岗位' name,
		dt as operate_time
	from (
		select
			sum(posts) as num,
			dt
		from cy_database.dws_enterprise_position_require_detail
		where dt = '2020-06-17' and ent_name is not null
		group by dt
		)a
),




tmp6_table as
(
select
	num,
	null percent1,
	'新增招聘岗位数' content,
	'新增招聘岗位数' name,
	dt as operate_time
from (
	select
		dt,
		sum(posts) as num
	from cy_database.dws_enterprise_position_require_detail
	group by dt
	)a
),


tmp7_table as
(
select
	num,
	cast((num/amount) as decimal(10,5) ) as percent1,
	content,
	'招聘岗位' name,
	dt as operate_time
from (
	select
		dt,
		content,
		num,
		sum(num) over() as amount
	from (
		select
			dt,
			business_type as content,
			sum(posts) as num
		from cy_database.dws_enterprise_position_require_detail
		group by dt,business_type
		having 	business_type is not null and business_type != 'null'
		)a
	)aa
),


tmp8_table as
(
select
	num,
	cast((num/amount) as decimal(10,5) ) as percent1,
	content,
	'行业分布' name,
	dt as operate_time
from (
	select
		dt,
		content,
		num,
		sum(num) over() as amount
	from (
		select
			dt,
			business_type as content,
			count(business_type) as num
		from cy_database.dws_enterprise_position_require_detail
		group by dt,business_type
		having 	business_type is not null and business_type != 'null' and business_type !=' '
		)a
	)aa
)


insert into table cy_database.ads_big_screen_result_ent
select num,percent1,content,name,operate_time from tmp1_ent_nature

union all
select num,percent1,content,name,operate_time from tmp2_table

union all
select num,percent1,content,name,operate_time from tmp3_table

union all
select num,percent1,content,name,operate_time from tmp4_table

union all
select num,percent1,content,name,operate_time from tmp5_table

union all
select num,percent1,content,name,operate_time from tmp6_table

union all
select num,percent1,content,name,operate_time from tmp7_table

union all
select num,percent1,content,name,operate_time from tmp8_table
;

"

$hive -e "$sql"